package org.example.repository;

import org.example.model.MaterialRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

@Repository
public class MaterialRequestDao {

    private final JdbcTemplate jdbcTemplate;

    public MaterialRequestDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    // 插入物料申请信息
    public int insertMaterialRequest(MaterialRequest request) {
        String sql = "INSERT INTO material_requests (request_id, material_name, quantity, unit_price,purchase_location, request_reason, approval_status, approval_opinion, approval_time) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        if(Objects.equals(request.getApprovalOpinion(), "")){
            request.setApprovalOpinion("无");
        }
        if(Objects.equals(request.getApprovalTime(), "")){
            request.setApprovalTime(null);
        }
        return jdbcTemplate.update(sql,
                request.getRequestId(),
                request.getMaterialName(),
                request.getQuantity(),
                request.getUnitPrice(),
                request.getPurchaseLocation(),
                request.getRequestReason(),
                request.getApprovalStatus(),
                request.getApprovalOpinion(),
                request.getApprovalTime());
    }

    // 查询所有物料申请信息
    public List<MaterialRequest> getAllMaterialRequests() {
        String sql = "SELECT * FROM material_requests";
        return jdbcTemplate.query(sql, new MaterialRequestRowMapper());
    }

    // 根据ID查询物料申请信息
    public MaterialRequest getMaterialRequestById(int requestId) {
        String sql = "SELECT * FROM material_requests WHERE request_id = ?";
        return jdbcTemplate.queryForObject(sql, new MaterialRequestRowMapper(), requestId);
    }

    // 更新物料申请信息
    public int updateMaterialRequest(MaterialRequest request) {
        String sql = "UPDATE material_requests SET " +
                "material_name = ?, " +
                "quantity = ?, " +
                "unit_price = ?, " +
                "purchase_location = ?, " +
                "request_reason = ?, " +
                "approval_status = ?, " +
                "approval_opinion = ?, " +
                "approval_time = ? " +
                "WHERE request_id = ?";
        if(Objects.equals(request.getApprovalOpinion(), "")){
            request.setApprovalOpinion("无");
        }
        if(Objects.equals(request.getApprovalTime(), "")){
            request.setApprovalTime(null);
        }
        return jdbcTemplate.update(sql,
                request.getMaterialName(),
                request.getQuantity(),
                request.getUnitPrice(),
                request.getPurchaseLocation(),
                request.getRequestReason(),
                request.getApprovalStatus(),
                request.getApprovalOpinion(),
                request.getApprovalTime(),
                request.getRequestId());
    }

    // 删除物料申请信息
    public int deleteMaterialRequest(int requestId) {
        String sql = "DELETE FROM material_requests WHERE request_id = ?";
        return jdbcTemplate.update(sql, requestId);
    }

    /**
     * 分页查询符合条件的物料申请数据
     * @param materialName 物料名称
     * @param approvalStatus 审批状态
     * @param pageable 分页参数
     * @return 符合查询条件的分页数据
     */
    public List<MaterialRequest> searchMaterialRequests(String materialName, String approvalStatus, Pageable pageable) {
        String sql = "SELECT * FROM material_requests WHERE 1=1 ";
        List<Object> params = new ArrayList<>();

        if (StringUtils.hasText(materialName)) {
            sql += " AND material_name LIKE ?";
            params.add("%" + materialName + "%");
        }

        if (StringUtils.hasText(approvalStatus)) {
            sql += " AND approval_status = ?";
            params.add(approvalStatus);
        }

        sql += " LIMIT ? OFFSET ?";
        params.add(pageable.getPageSize());
        params.add(pageable.getOffset());

        return jdbcTemplate.query(sql, new MaterialRequestRowMapper(), params.toArray());
    }

    /**
     * 返回分页查询符合条件的物料申请数据总记录条数
     * @param materialName 物料名称
     * @param approvalStatus 审批状态
     * @return 符合查询条件的记录总数
     */
    public int countSearchMaterialRequests(String materialName, String approvalStatus) {
        String sql = "SELECT COUNT(*) FROM material_requests WHERE 1=1 ";
        List<Object> params = new ArrayList<>();

        if (StringUtils.hasText(materialName)) {
            sql += " AND material_name LIKE ?";
            params.add("%" + materialName + "%");
        }

        if (StringUtils.hasText(approvalStatus)) {
            sql += " AND approval_status = ?";
            params.add(approvalStatus);
        }

        return jdbcTemplate.queryForObject(sql, Integer.class, params.toArray());
    }

    /**
     * 结果集映射类,实现ORM的关系到对象的映射
     */
    private static class MaterialRequestRowMapper implements RowMapper<MaterialRequest> {
        @Override
        public MaterialRequest mapRow(ResultSet rs, int rowNum) throws SQLException {
            MaterialRequest obj = new MaterialRequest();
            obj.setRequestId(rs.getInt("request_id"));
            obj.setMaterialName(rs.getString("material_name"));
            obj.setQuantity(rs.getInt("quantity"));
            obj.setUnitPrice(rs.getDouble("unit_price"));
            obj.setTotalAmount(rs.getDouble("total_amount"));
            obj.setPurchaseLocation(rs.getString("purchase_location"));
            obj.setRequestReason(rs.getString("request_reason"));
            obj.setApprovalStatus(rs.getString("approval_status"));
            obj.setApprovalOpinion(rs.getString("approval_opinion"));
            obj.setApprovalTime(rs.getString("approval_time"));
            return obj;
        }
    }
}